
package hermit.forum.db;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Vector;
import hermit.forum.log.Log;



/**
 * Базовый клас для работы с базой данных.
 * 
 */
public abstract class SQLite {
    private Connection db;
    public File fileDb;
    private long lastConnect = 0;
    private Log logger;
    
    public SQLite() throws Exception {
        this.logger = new Log();
    }   

    public SQLite(Log logger) throws Exception {
        this.logger = logger;
    }
    
    public static Timestamp getTS(Timestamp t){
        return t.equals(new Timestamp(0)) ? null : t;
    }
    
    public boolean isClosed() {
    	if(db==null) return true;
    	try {
    		return db.isClosed();
    	} catch (Exception ex) {
                logger.err(ex.getMessage());
    		return true;
    	}
    }
    
    /**
     * Закрываем соединение с БД
     */
    public void shutdown(){
            try {
                db.close();
            } catch (SQLException ex) {
            	logger.err(ex.getMessage());
            }
    }
    
    public boolean openConnection(File file) {
        	while(!open(file)){
                //  System.out.println("Connected to: " + file.getName());
        	}
        	return true;
    }

    /**
      * проверка наличия файла или дирректории
      * @param file
      * @return
      */
      public boolean testFile(File file) {
           if (file.length()==0)file.delete();
           return file.exists();
     }
    //тут надо создать базу данных
    public abstract void createDB(String pathDb);
    
    public boolean open(File file) {
        boolean f=false;
        if (!testFile(file)) createDB(file.getParent());
        String base =file.getPath();
        fileDb = file;
        try {
            if (db != null) {
                if (!db.isClosed()) {
                    logger.db("Connected to: " + file.getName());
                    //System.out.println("[essence] - Connected to: " + file.getName());
                    return true;
                }
            }
        } catch (Exception ex) {
             logger.err(ex.getMessage());
           // ex.printStackTrace();
        }
          
        // Исключаем слишком частые подключения к БД
        if((System.currentTimeMillis()-lastConnect)<30000)
            return false;
            try {
                Class.forName("org.sqlite.JDBC");
                db = DriverManager.getConnection("jdbc:sqlite:" + base);
            } catch (Exception ex) {
                ex.printStackTrace();
                f=false;
                lastConnect = System.currentTimeMillis();
                logger.err("Ошибка подключения к базе данных!!!");
                //System.err.println("[essence] - Ошибка подключения к базе данных!!!");
            }
        return f;
    }    
    
    public void executeQuery(String qry) {
    	Statement stmt = null;
        try {
            stmt = getDb().createStatement();
      //      System.out.println("EXEC: " + qry);
            stmt.execute(qry);
        } catch (Exception ex) {
            logger.err(ex.getMessage());
        } finally {
        	if(stmt!=null) try{stmt.close();} catch(Exception e) {};
        }
    }
    
    public Vector<String[]> getValues(String query) {
        Vector<String[]> v = new Vector<String[]>();
        String[] ss;
        ResultSet rst=null;
        Statement stmt=null;
        try {
        	stmt = getDb().createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        //	System.out.println("EXEC: " + query);
        	rst = stmt.executeQuery(query);

           do {
                ss = readNext(rst);
                if (ss != null){
                    v.addElement(ss);
                    ss = readNext(rst);
                }
                else break;
            }while(rst.next());
        } catch (Exception ex) {
            logger.err(ex.getMessage());
        } finally {
        	if(rst!=null) try{rst.close();} catch(Exception e) {};
        	if(stmt!=null) try{stmt.close();} catch(Exception e) {};
        }
        return v;
    }

  public String[] readNext(ResultSet rSet) {
      String sTmp[]=null;
      int i;
      try {
        if (rSet.next()) {
          sTmp=new String [rSet.getMetaData().getColumnCount()];
          for (i = 1; i <= rSet.getMetaData().getColumnCount(); i++) {
            //if (i > 1) sTmp += '\t';
            sTmp[i-1] = rSet.getString(i);
          }
        } else sTmp=null;
      }
      catch(SQLException ex) {
        logger.err(ex.getMessage());
        sTmp=null;
      }
      return sTmp;
    }

    public long getLastIndex(String tableName) {
        String s="";
        String q = "select max(id) as id from " + tableName;
        ResultSet rst=null;
        Statement stmt=null;
        try {
        	stmt = getDb().createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      //  	System.out.println("EXEC: " + q);
        	rst = stmt.executeQuery(q);
            s = readNext(rst)[0];
        } catch (Exception ex) {
            s="";
        } finally {
        	if(rst!=null) try{rst.close();} catch(Exception e) {};
        	if(stmt!=null) try{stmt.close();} catch(Exception e) {};
        }
        if (s==null) return 0;
        if (s.equals("")) return 0;
        if (Long.parseLong(s) < 0) return 0;
        return Long.parseLong(s) + 1;
    }
    


    /**
     * Возвращает Connection текущей БД.
     * При необходимости производит подключение и инициализацию
     * @return
     * @throws SQLException 
     */
	public Connection getDb() throws SQLException {
		if(db.isClosed()){
			openConnection(fileDb);
		}
		return db;
	}
}